﻿CREATE PROCEDURE [dbo].[users_delete]
@userId int

AS 
DELETE FROM Responses
WHERE Responses.UserId=@userId;

DELETE FROM [dbo].[Favorites]
WHERE Favorites.UserId = @userId

DELETE FROM [dbo].[Ratings]
WHERE Ratings.UserId = @userId

/*****Start delete books******/
DECLARE @bookId int = 0
DECLARE BooksDeleteCursor CURSOR STATIC LOCAL FOR
        SELECT Books.BookId
        FROM Books
        WHERE Books.UserId = @userId
OPEN BooksDeleteCursor 
    FETCH NEXT FROM BooksDeleteCursor INTO @bookId
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC books_delete @bookId
        FETCH NEXT FROM BooksDeleteCursor INTO @bookId
    END
CLOSE BooksDeleteCursor
/*****End delete books******/

/*****Start delete messages******/
DECLARE @messageId int = 0
DECLARE MessageSenderDeleteCursor CURSOR STATIC LOCAL FOR
        SELECT [Messages].MessageId
        FROM [Messages]
        WHERE [Messages].SenderId = @userId OR [Messages].RecipientId = @userId
OPEN MessageSenderDeleteCursor 
    FETCH NEXT FROM MessageSenderDeleteCursor INTO @messageId
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC message_delete @messageId
        FETCH NEXT FROM MessageSenderDeleteCursor INTO @messageId
    END
CLOSE MessageSenderDeleteCursor
/*****End delete messages******/

/*****Start delete messages******/
DECLARE @friendId int = 0
DECLARE FriendDeleteCursor CURSOR STATIC LOCAL FOR
        SELECT Friends.FriendId
        FROM Friends
        WHERE Friends.UserId = @userId
OPEN FriendDeleteCursor 
    FETCH NEXT FROM FriendDeleteCursor INTO @friendId
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC friends_delete @friendId, @userId
        FETCH NEXT FROM FriendDeleteCursor INTO @friendId
    END
CLOSE FriendDeleteCursor

DECLARE FriendUserDeleteCursor CURSOR STATIC LOCAL FOR
        SELECT Friends.UserId
        FROM Friends
        WHERE Friends.FriendId = @userId
OPEN FriendUserDeleteCursor 
    FETCH NEXT FROM FriendUserDeleteCursor INTO @friendId
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC friends_delete @friendId, @userId
        FETCH NEXT FROM FriendUserDeleteCursor INTO @friendId
    END
CLOSE FriendUserDeleteCursor
/*****End delete messages******/

DELETE FROM [dbo].[Users]
WHERE Users.[UserId]=@userId

RETURN 0